CREATE TABLE [dbo].[StateProvinceRef]
(
[StateProvinceCode] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CountryCode] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_StateProvinceRef_CountryCode] DEFAULT (''),
[StateProvinceDesc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_StateProvinceRef_StateProvinceDesc] DEFAULT (''),
[ChapterGroupKey] [uniqueidentifier] NULL,
[IsActive] [bit] NOT NULL,
[IsHandModified] [bit] NOT NULL,
[UpdatedByUserKey] [uniqueidentifier] NOT NULL,
[UpdatedOn] [datetime] NOT NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[StateProvinceRef] ADD CONSTRAINT [PK_StateProvinceRef] PRIMARY KEY CLUSTERED ([CountryCode], [StateProvinceCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_StateProvinceRef_ChapterGroupKey] ON [dbo].[StateProvinceRef] ([ChapterGroupKey]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_StateProvinceRef_StateProvinceCode] ON [dbo].[StateProvinceRef] ([StateProvinceCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_StateProvinceRef_UpdatedByUserKey] ON [dbo].[StateProvinceRef] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StateProvinceRef] ADD CONSTRAINT [FK_StateProvinceRef_CountryRef] FOREIGN KEY ([CountryCode]) REFERENCES [dbo].[CountryRef] ([CountryCode])
GO
ALTER TABLE [dbo].[StateProvinceRef] ADD CONSTRAINT [FK_StateProvinceRef_GroupMain_Chapter] FOREIGN KEY ([ChapterGroupKey]) REFERENCES [dbo].[GroupMain] ([GroupKey])
GO
ALTER TABLE [dbo].[StateProvinceRef] ADD CONSTRAINT [FK_StateProvinceRef_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO